package com.test.connection_hive;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Locale;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpSession;

import com.test.classs.hd_student;
import com.test.page.PageInfo;

public class hive_SQL {

	private static String driverName = "org.apache.hadoop.hive.jdbc.HiveDriver";
	public static Statement stmt;

	public static void main(String[] args) throws Exception {
		long start = System.currentTimeMillis();
		connectionHive();
		// countActiveuser(stmt);
		// getCount("id","q");
		System.out.println(getCount("id", "hd_student"));
		HttpServletRequest request = null;
		test(request);

		long end = System.currentTimeMillis();
		System.out.println("time = " + (end - start) / (1000));
	}

	/**
	 * @author qinzhen
	 * 
	 */
	public static void test(HttpServletRequest request) {

		PageInfo pageInfo = null;
		List<hd_student> studentList = null;
		try {
			connectionHive();
			pageInfo = new PageInfo(request);
			pageInfo.setPageSize(5);
			studentList = hive_SQL.selectAllHd_student(hive_SQL.stmt, pageInfo);
			System.out.println("studentList=" + studentList);
		} catch (Exception e) {
			e.printStackTrace();
		}

	}

	/**
	 * @author qinzhen
	 * @function 杩炴帴linux涓殑hive
	 * @return Statement
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 * @throws Exception
	 */
	public static void connectionHive() throws ClassNotFoundException,
			SQLException, Exception {

		Class.forName(driverName);
		Connection con = DriverManager.getConnection(
				"jdbc:hive://hadoop:10000/studentmanagesystem", "", "");
		stmt = con.createStatement();

		// return stmt;

	}

	/**
	 * @author qinzhen
	 * @param id
	 * @param tableName
	 * @return 条数
	 * @function 统计总条数
	 */
	public static int getCount(String id, String tableName) {

		try {
			// Statement stmt =connectionHive() ;
			// String sql = "select count("+id+") as counts from " + tableName ;
			String sql = "select count(" + id + ") from studentmanagesystem."
					+ tableName;
			ResultSet rs = stmt.executeQuery(sql);

			int count = 0;
			if (rs.next()) {
				count = rs.getInt(1);
			}
			rs.close();
			return count;

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return 0;
	}

	/**
	 * @author qinzhen
	 * @time 2015/3/14
	 * @param stmt
	 * @param pageInfo
	 * @param fieldName
	 * @param value
	 * @return
	 * @throws SQLException
	 * @throws Exception
	 * @function 根据字段名称和值，查询学生信息
	 */
	public static List<hd_student> selectHd_studentFromFieldName(Statement stmt,PageInfo pageInfo,
			String fieldName, String value) throws SQLException, Exception {

		String querySQL = "select * from studentmanagesystem.hd_student where "
				+ fieldName + "='" + value + "\'";
		
//		if (pageInfo != null) {
//			querySQL += ("where cid>=" + pageInfo.getCurrentPageNow()
//					+ " and cid<= " + pageInfo.getPageSize());
//			pageInfo.setTotalResult(getCount("id", "hd_student"));
//
//		}
		List<hd_student> studentList = new ArrayList<hd_student>();
		ResultSet res = stmt.executeQuery(querySQL);

		while (res.next()) {
			hd_student student = new hd_student();

			byte[] b = res.getString("address").getBytes("iso-8859-1");// 缂栫爜
			String sa = new String(b, "gbk");//
			System.out.println(sa + " m  = " + res.getString("address"));

			student.setCid(res.getString("cid"));
			student.setId(res.getString("id"));
			student.setAddress(res.getString("address"));
			student.setBirthday(res.getString("birthday"));
			student.setCollege(res.getString("college"));
			student.setEmail(res.getString("email"));
			student.setEndschoolYear(res.getString("endschoolyear"));
			student.setIdCard(res.getString("idcard"));
			student.setIsDelete(res.getString("isdelete"));
			student.setMsg(res.getString("msg"));
			student.setName(res.getString("name"));
			student.setNation(res.getString("nation"));
			student.setPassWord(res.getString("password"));
			student.setQQ(res.getString("qq"));
			student.setSex(res.getString("sex"));
			student.setStartSchoolYear(res.getString("startschoolyear"));
			student.setState(res.getString("state"));
			student.setStuId(res.getString("stuid"));
			student.setTrainingLevel(res.getString("traininglevel"));
			student.setPhone(res.getString("phone"));
			student.setPic(res.getString("pic"));
			student.setPolitical(res.getString("political"));
			student.setProfessionalId(res.getString("professionalid"));
			studentList.add(student);
		}
		return studentList;
	}

	/**
	 * @author qinzhe
	 * @time 2015/3/14
	 * @param stmt
	 * @param pageInfo
	 * @return List<hd_student>
	 * @throws SQLException
	 * @throws Exception
	 * @function 分页查询所有学生信息数据
	 */
	public static List<hd_student> selectAllHd_student(Statement stmt,
			PageInfo pageInfo) throws SQLException, Exception {
//		System.out.println("pageInfo.getCurrentPageNow()="
//				+ pageInfo.getCurrentPageNow() + " "
//				+ "pageInfo.getPageSize()=" + pageInfo.getPageSize());
		// querySQL="select * from studentmanagesystem.hd_student where name='"+loginname+"\'";
		String querySQL = "select * from studentmanagesystem.hd_student ";
		if (pageInfo != null) {
			querySQL += ("where cid>=" + pageInfo.getCurrentPageNow()
					+ " and cid<= " + pageInfo.getPageSize());
			pageInfo.setTotalResult(getCount("id", "hd_student"));

		}
		List<hd_student> studentList = new ArrayList<hd_student>();
		ResultSet res = stmt.executeQuery(querySQL);
		while (res.next()) {
			hd_student student = new hd_student();
			byte[] b = res.getString("address").getBytes("iso-8859-1");// 缂栫爜
			String sa = new String(b, "gbk");//
			System.out.println(sa + " m  = " + res.getString("address"));
			student.setCid(res.getString("cid"));
			student.setId(res.getString("id"));
			student.setAddress(res.getString("address"));
			student.setBirthday(res.getString("birthday"));
			student.setCollege(res.getString("college"));
			student.setEmail(res.getString("email"));
			student.setEndschoolYear(res.getString("endschoolyear"));
			student.setIdCard(res.getString("idcard"));
			student.setIsDelete(res.getString("isdelete"));
			student.setMsg(res.getString("msg"));
			student.setName(res.getString("name"));
			student.setNation(res.getString("nation"));
			student.setPassWord(res.getString("password"));
			student.setQQ(res.getString("qq"));
			student.setSex(res.getString("sex"));
			student.setStartSchoolYear(res.getString("startschoolyear"));
			student.setState(res.getString("state"));
			student.setStuId(res.getString("stuid"));
			student.setTrainingLevel(res.getString("traininglevel"));
			student.setPhone(res.getString("phone"));
			student.setPic(res.getString("pic"));
			student.setPolitical(res.getString("political"));
			student.setProfessionalId(res.getString("professionalid"));
			studentList.add(student);
		}
		return studentList;
	}

}
